﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Configuration;

namespace sow
{
	/// <summary>
	/// Summary description for emailcompose.
	/// </summary>
	public partial class emaillist : System.Web.UI.Page
	{
		int EmailListID = -1;
		int UserID;
		protected System.Web.UI.WebControls.Button Button1;
		protected System.Web.UI.WebControls.TextBox TextBox_Account;
		protected System.Web.UI.WebControls.TextBox TextBox_Password;
		protected System.Web.UI.WebControls.TextBox TextBox_SmtpServer;
		protected System.Web.UI.WebControls.TextBox TextBox_LogDir;
		protected System.Web.UI.WebControls.CheckBox CheckBox_State;

		protected void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
			HttpCookie emailCookie = Request.Cookies["Email"];
			if(emailCookie == null)
				Response.Redirect("index.aspx");

			DataSet ds = EmailDataAccess.GetUser(emailCookie.Value);
			UserID = Convert.ToInt32(ds.Tables[0].Rows[0]["UserID"]);

			if(Request["ListID"] != null)
			{
				EmailListID = Convert.ToInt32(Request["ListID"]);
				ds = EmailDataAccess.GetEmailListByEmailListID(EmailListID);
				this.Label_Title.Text = ds.Tables[0].Rows[0]["ListName"].ToString() + "：";
				//this.Button_EditList.Attributes["onclick"] = "location.href='emaillistEdit.aspx?ListID=" + EmailListID.ToString() + "';return false;";
				this.Button_DeleteList.Attributes["onclick"] = "if(!confirm('确实要删除“" + this.Label_Title.Text + "”吗？')){return false;}";
				this.Button_DeleteList.Visible = true;
				this.Button_AddAddress.Enabled = true;
				//this.Button_EditList.Visible = true;
				this.Button_AddAddress.Enabled = true;
			}
			else
			{
				this.Label_Title.Text = "";
				this.Button_DeleteList.Visible = false;
				this.Button_EditList.Visible = false;
				this.Button_DeleteList.Visible = false;
				this.Button_AddAddress.Enabled = false;
			}

			//this.Button_AddList.Attributes["onclick"] = "location.href='emaillistEdit.aspx';return false;";
			//this.Button_AddAddress.Attributes["onclick"] = "if(!checkEmailStr()){document.all('TextBox_EmailAddress').focus();return false}";

			if (!IsPostBack) 
			{
				BindDataGrid();
			
			}

			int listnumsize = 10;
			int emailnumsize = 50;
			ds = EmailDataAccess.GetDataSet("EmailAccount", "");
			if(ds.Tables[0].Rows.Count > 0)
			{
				listnumsize = Convert.ToInt32(ds.Tables[0].Rows[0]["EmailListSize"]);
				emailnumsize = Convert.ToInt32(ds.Tables[0].Rows[0]["EmailSize"]);
			}
			ds = EmailDataAccess.GetEmailListByUserID(UserID);
			if(ds.Tables[0].Rows.Count >= listnumsize)
				this.Button_AddList.Visible = false;
			else
				this.Button_AddList.Visible = true;


			ds = EmailDataAccess.GetEmailAddressByUserID(UserID);
			if(ds.Tables[0].Rows.Count >= emailnumsize)
				this.Button_AddAddress.Visible = false;
			else
				this.Button_AddAddress.Visible = true;

		}

		#region Web Form Designer generated code
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: This call is required by the ASP.NET Web Form Designer.
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{    
			this.DataGrid_List.ItemCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid_List_ItemCommand);
			this.DataGrid_List.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.DataGrid_List_ItemDataBound);

		}
		#endregion

		private void BindDataGrid()
		{
			DataSet ds = EmailDataAccess.GetEmailListByUserID(UserID);
			this.Literal1.Text = "<table border=0 align=center><tr><td height=30>";
			this.Literal1.Text += "<font size=2>你的所有电子邮件列表如下:</font></td><tr>\n";
			foreach(DataRow dr in ds.Tables[0].Rows)
			{
				this.Literal1.Text += "<tr><td height=30><a href='emaillist.aspx?listid=" + dr["EmailListID"].ToString() + "'><font size=2>" +
					dr["ListName"].ToString() + "</font></a></td></tr>\n";
			}
			this.Literal1.Text += "</table>";

			if(EmailListID > 0)
			{
				ds = EmailDataAccess.GetEmailAddressByEmailListID(EmailListID);
				//}
				//else
				//{
				//	ds = EmailDataAccess.GetEmailAddressByUserID(UserID);
				//}

				this.DataGrid_List.DataSource = ds.Tables[0].DefaultView;
				this.DataGrid_List.DataBind();
			}
		}

	    private void DataGrid_List_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
		{
			if(e.Item.ItemIndex < 0)
				return;

			DataRowView drv = (DataRowView)e.Item.DataItem;
			Label label = (Label)e.Item.FindControl("Label_ID");
			label.Text = EmailListID.ToString() + "," + drv.Row["EmailID"].ToString();

			Button button = (Button)e.Item.FindControl("Button_Delete");
			button.CommandName = "DELETE";
			button.Attributes["onclick"] = "if(!confirm('确实要删除" + drv.Row["EmailAddress"].ToString() + "吗？')){return false;}";
		}

		private void DataGrid_List_ItemCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
		{
			if(e.CommandName == "DELETE")
			{
				Label label = (Label)e.Item.FindControl("Label_ID");
				string[] ids = label.Text.Split(',');
				if(Convert.ToInt32(ids[0]) > 0)
					EmailDataAccess.DeleteEmailAndList(ids[0], ids[1]);
				else
					EmailDataAccess.DeleteEmail(ids[1]);

				this.BindDataGrid();
			}
		}

		protected void Button_AddList_Click1(object sender, EventArgs e)
        {
            if (this.TextBox1.Text.Trim().Length > 0)
            {
                EmailListID = EmailDataAccess.InsertEmailList(UserID, this.TextBox1.Text.Trim());
                Response.Redirect("emaillist.aspx?listid=" + EmailListID);
            }

        }

        protected void Button_EditList_Click(object sender, EventArgs e)
        {

        }

        protected void Button_DeleteList_Click1(object sender, EventArgs e)
        {
            if (EmailListID > 0)
            {
                EmailDataAccess.DeleteEmailList(EmailListID.ToString());
                Response.Redirect("emaillist.aspx");
            }
        }

        protected void Button_AddAddress_Click1(object sender, EventArgs e)
        {
            if (TextBox_EmailAddress.Text.Trim().Length > 0)
            {
                string[] arrEmail = TextBox_EmailAddress.Text.Trim().Split(',');
                for (int i = 0; i < arrEmail.Length; i++)
                {
                    int EmailID = EmailDataAccess.InsertEmailAddress(UserID, arrEmail[i]);
                    if (EmailID > 0 && EmailListID > 0)
                    {
                        EmailDataAccess.InsertEmailAndList(EmailListID, EmailID);
                    }
                }
                this.BindDataGrid();
            }
        }

        protected void TextBox_EmailAddress_TextChanged(object sender, EventArgs e)
        {

        }
	}


	public class EmailDataAccess
	{
		private const string DBConenctionKey = "DBConnStr";

		public static int InsertEmailList(int UserID, string ListName)
		{
			SqlCommand cmd = new SqlCommand("sp_InsertEmailList");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraUserID = cmd.CreateParameter();
			paraUserID.SqlDbType = SqlDbType.Int;
			paraUserID.ParameterName = "@UserID";
			paraUserID.Value = UserID;
			cmd.Parameters.Add(paraUserID);

			SqlParameter paraListName = cmd.CreateParameter();
			paraListName.SqlDbType = SqlDbType.NVarChar;
			paraListName.ParameterName = "@ListName";
			paraListName.Value = ListName;
			cmd.Parameters.Add(paraListName);

			SqlParameter parareturnid = cmd.CreateParameter();
			parareturnid.SqlDbType = SqlDbType.Int;
			parareturnid.ParameterName = "@ReturnID";
			parareturnid.Direction = ParameterDirection.Output;
			parareturnid.Value = -1;
			cmd.Parameters.Add(parareturnid);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			
			cmd.ExecuteNonQuery();
			cn.Close();
			
			return Convert.ToInt32(parareturnid.Value);
		}


		public static int InsertEmailAddress(int UserID, string EmailAddress)
		{
			SqlCommand cmd = new SqlCommand("sp_InsertEmailAddress");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraUserID = cmd.CreateParameter();
			paraUserID.SqlDbType = SqlDbType.Int;
			paraUserID.ParameterName = "@UserID";
			paraUserID.Value = UserID;
			cmd.Parameters.Add(paraUserID);

			SqlParameter paraEmailAddress = cmd.CreateParameter();
			paraEmailAddress.SqlDbType = SqlDbType.NVarChar;
			paraEmailAddress.ParameterName = "@EmailAddress";
			paraEmailAddress.Value = EmailAddress;
			cmd.Parameters.Add(paraEmailAddress);

			SqlParameter parareturnid = cmd.CreateParameter();
			parareturnid.SqlDbType = SqlDbType.Int;
			parareturnid.ParameterName = "@ReturnID";
			parareturnid.Direction = ParameterDirection.Output;
			parareturnid.Value = -1;
			cmd.Parameters.Add(parareturnid);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			
			cmd.ExecuteNonQuery();
			cn.Close();
			
			return Convert.ToInt32(parareturnid.Value);
		}


		public static void InsertEmailAndList(int EmailListID, int EmailID)
		{
			SqlCommand cmd = new SqlCommand("sp_InsertEmailAndList");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraEmailListID = cmd.CreateParameter();
			paraEmailListID.SqlDbType = SqlDbType.Int;
			paraEmailListID.ParameterName = "@EmailListID";
			paraEmailListID.Value = EmailListID;
			cmd.Parameters.Add(paraEmailListID);

			SqlParameter paraEmailID = cmd.CreateParameter();
			paraEmailID.SqlDbType = SqlDbType.Int;
			paraEmailID.ParameterName = "@EmailID";
			paraEmailID.Value = EmailID;
			cmd.Parameters.Add(paraEmailID);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			
			cmd.ExecuteNonQuery();
			cn.Close();
		}


		public static void InsertQueuedEmail(int UserID, string From, string To, string Subject, string Body)
		{
			SqlCommand cmd = new SqlCommand("sp_InsertQueuedEmail");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraUserID = cmd.CreateParameter();
			paraUserID.SqlDbType = SqlDbType.Int;
			paraUserID.ParameterName = "@UserID";
			paraUserID.Value = UserID;
			cmd.Parameters.Add(paraUserID);

			SqlParameter paraFrom = cmd.CreateParameter();
			paraFrom.SqlDbType = SqlDbType.NVarChar;
			paraFrom.ParameterName = "@From";
			paraFrom.Value = From;
			cmd.Parameters.Add(paraFrom);

			SqlParameter paraTo = cmd.CreateParameter();
			paraTo.SqlDbType = SqlDbType.NVarChar;
			paraTo.ParameterName = "@To";
			paraTo.Value = To;
			cmd.Parameters.Add(paraTo);

			SqlParameter paraSubject = cmd.CreateParameter();
			paraSubject.SqlDbType = SqlDbType.NVarChar;
			paraSubject.ParameterName = "@Subject";
			paraSubject.Value = Subject;
			cmd.Parameters.Add(paraSubject);

			SqlParameter paraBody = cmd.CreateParameter();
			paraBody.SqlDbType = SqlDbType.NVarChar;
			paraBody.ParameterName = "@Body";
			paraBody.Value = Body;
			cmd.Parameters.Add(paraBody);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			
			cmd.ExecuteNonQuery();
			cn.Close();
		}

		public static DataSet GetUser(string email)
		{
			string sqlstr = "SELECT u.*,r.RoleID as Role FROM Users as u left join UserRoles as r on u.UserID = r.UserID  WHERE Email='" + email + "'";
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}

        public static bool hasUserRole(HttpCookie email, int roleID) 
		{
            if (email == null || email.Value == null)
            {
                return false;  
            }
			DataSet ds = EmailDataAccess.GetUser(email.Value);
            if (ds != null)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (Convert.ToInt16(ds.Tables[0].Rows[i]["Role"]) == roleID)
                    {
                        return true;
                    }
                }
            }
			return false;
		}

		public static DataSet GetEmailListByUserID(int UserID)
		{
			string sqlstr = "SELECT * FROM EmailList WHERE UserID=" + UserID.ToString();
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}


		public static DataSet GetEmailListByEmailListID(int EmailListID)
		{
			string sqlstr = "SELECT * FROM EmailList WHERE EmailListID=" + EmailListID.ToString();
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}


		public static DataSet GetEmailAddressByUserID(int UserID)
		{
			string sqlstr = "SELECT * FROM EmailAddress WHERE UserID=" + UserID.ToString();
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}


		public static DataSet GetEmailAddressByEmailListID(int EmailListID)
		{
			string sqlstr = "SELECT * FROM EmailAddress WHERE EmailID IN (SELECT EmailID FROM EmailAndList WHERE EmailListID=" + EmailListID.ToString() + ")";
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}

		public static DataSet GetDataSet(string tblname, string filter)
		{
			string sqlstr;
			if(filter != "")
				sqlstr = "SELECT * FROM " + tblname + " WHERE " + filter;
			else
				sqlstr = "SELECT * FROM " + tblname;
			SqlCommand cmd = new SqlCommand(sqlstr);
			cmd.CommandType = CommandType.Text;

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = new DataSet();
			da.Fill(ds);
			cn.Close();

			return ds;
		}


		public static bool DeleteEmail(string EmailID)
		{
			SqlCommand cmd = new SqlCommand("sp_DeleteEmail");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter para = cmd.CreateParameter();
			para.SqlDbType = SqlDbType.Int;
			para.ParameterName = "@EmailID";
			para.Value = Convert.ToInt64(EmailID);
			cmd.Parameters.Add(para);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			cmd.ExecuteNonQuery();
			
			cn.Close();

			return true;
		}


		public static bool DeleteEmailList(string EmailListID)
		{
			SqlCommand cmd = new SqlCommand("sp_DeleteEmailList");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter para = cmd.CreateParameter();
			para.SqlDbType = SqlDbType.Int;
			para.ParameterName = "@EmailListID";
			para.Value = Convert.ToInt64(EmailListID);
			cmd.Parameters.Add(para);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			cmd.ExecuteNonQuery();
			
			cn.Close();

			return true;
		}

		public static bool DeleteEmailAndList(string EmailListID, string EmailID)
		{
			SqlCommand cmd = new SqlCommand("sp_DeleteEmailAndList");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter para = cmd.CreateParameter();
			para.SqlDbType = SqlDbType.Int;
			para.ParameterName = "@EmailListID";
			para.Value = Convert.ToInt64(EmailListID);
			cmd.Parameters.Add(para);

			SqlParameter paraEmailID = cmd.CreateParameter();
			paraEmailID.SqlDbType = SqlDbType.Int;
			paraEmailID.ParameterName = "@EmailID";
			paraEmailID.Value = Convert.ToInt64(EmailID);
			cmd.Parameters.Add(paraEmailID);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			cmd.ExecuteNonQuery();
			
			cn.Close();

			return true;
		}


		public static bool DeleteEmailAndListByEmailListID(string EmailListID)
		{
			SqlCommand cmd = new SqlCommand("sp_DeleteEmailAndListByEmailListID");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter para = cmd.CreateParameter();
			para.SqlDbType = SqlDbType.Int;
			para.ParameterName = "@EmailListID";
			para.Value = Convert.ToInt64(EmailListID);
			cmd.Parameters.Add(para);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			cmd.ExecuteNonQuery();
			
			cn.Close();

			return true;
		}


		public static bool UpdateEmailList(int EmailListID, string ListName)
		{
			SqlCommand cmd = new SqlCommand("sp_UpdateEmailList");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraEmailListID = cmd.CreateParameter();
			paraEmailListID.SqlDbType = SqlDbType.Int;
			paraEmailListID.ParameterName = "@EmailListID";
			paraEmailListID.Value = EmailListID;
			cmd.Parameters.Add(paraEmailListID);

			SqlParameter paraListName = cmd.CreateParameter();
			paraListName.SqlDbType = SqlDbType.NVarChar;
			paraListName.ParameterName = "@ListName";
			paraListName.Value = ListName;
			cmd.Parameters.Add(paraListName);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			cmd.ExecuteNonQuery();
			
			cn.Close();

			return true;
		}


		public static void UpdateEmailAccount(string EmailAccount, string AccountPassword, string SmtpServer, 
			string LogDir, int EmailListSize, int EmailSize, string State)
		{
			SqlCommand cmd = new SqlCommand("sp_UpdateEmailAccount");
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter paraEmailAccount = cmd.CreateParameter();
			paraEmailAccount.SqlDbType = SqlDbType.NVarChar;
			paraEmailAccount.ParameterName = "@EmailAccount";
			paraEmailAccount.Value = EmailAccount;
			cmd.Parameters.Add(paraEmailAccount);

			SqlParameter paraAccountPassword = cmd.CreateParameter();
			paraAccountPassword.SqlDbType = SqlDbType.NVarChar;
			paraAccountPassword.ParameterName = "@AccountPassword";
			paraAccountPassword.Value = AccountPassword;
			cmd.Parameters.Add(paraAccountPassword);

			SqlParameter paraSmtpServer = cmd.CreateParameter();
			paraSmtpServer.SqlDbType = SqlDbType.NVarChar;
			paraSmtpServer.ParameterName = "@SmtpServer";
			paraSmtpServer.Value = SmtpServer;
			cmd.Parameters.Add(paraSmtpServer);

			SqlParameter paraLogDir = cmd.CreateParameter();
			paraLogDir.SqlDbType = SqlDbType.NVarChar;
			paraLogDir.ParameterName = "@LogDir";
			paraLogDir.Value = LogDir;
			cmd.Parameters.Add(paraLogDir);

			SqlParameter paraEmailListSize = cmd.CreateParameter();
			paraEmailListSize.SqlDbType = SqlDbType.Int;
			paraEmailListSize.ParameterName = "@EmailListSize";
			paraEmailListSize.Value = EmailListSize;
			cmd.Parameters.Add(paraEmailListSize);

			SqlParameter paraEmailSize = cmd.CreateParameter();
			paraEmailSize.SqlDbType = SqlDbType.Int;
			paraEmailSize.ParameterName = "@EmailSize";
			paraEmailSize.Value = EmailSize;
			cmd.Parameters.Add(paraEmailSize);

			SqlParameter paraState = cmd.CreateParameter();
			paraState.SqlDbType = SqlDbType.NVarChar;
			paraState.ParameterName = "@State";
			paraState.Value = State;
			cmd.Parameters.Add(paraState);

			SqlConnection cn = new SqlConnection();
			cn.ConnectionString =  ConfigurationSettings.AppSettings[DBConenctionKey];
			cn.Open();

			cmd.Connection = cn;
			
			cmd.ExecuteNonQuery();
			cn.Close();
		}



	}
}
